﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using SLS.ExClassLib.Events;
/*
 * 创建新纪录时，首先根据模板查询，然后将值传入新纪录
 */
namespace LanSunERP
{

    public partial class SQLFunction
    { 
        public DataSet BuildAndRunCmdSQL(DataRow dwRequest, DataRow dwForeignKeyList)
        {
            //Tracer.Verbose("BuildAndRunCmdSQL.dwRequest", dwRequest);
            //Tracer.Verbose("BuildAndRunCmdSQL.dwForeignKeyList:", dwForeignKeyList);
            string strServiceContent, strServicName, strServiceConditions, strServiceTargetTable, strServiceTargetTableID, strSQL, strServiceType, strtable_name_list;
            try
            {
                strServiceContent = "EXECUTE [LANSUNERP].[DBO].[GENERATE_SQL] @table_name,@type,@conditions,@Sortby,@resultsql_1 OUTPUT,@resultsql_2 OUTPUT";
                strServicName = dwForeignKeyList["ForeignKey"].ToString().TrimEnd();
                strServiceConditions = dwForeignKeyList["Conditions"].ToString().Trim();
                strServiceType = dwForeignKeyList["Type"].ToString().TrimEnd();
                strServiceTargetTable = dwForeignKeyList["TBNAME"].ToString().TrimEnd();
                strServiceTargetTableID = "";
                if (TBTableList != null)
                {
                    dwTableLists = TBTableList.Select("TBNAME='" + strServiceTargetTable + "'");
                    strServiceTargetTableID = dwTableLists[0]["TableID"].ToString().TrimEnd();
                }
                string strSortby = "1";
                if (strServiceType == "PrimaryKey" || strServiceType == "Index")
                {
                    strServiceType = "SELECT";
                }
                cmdBuildingSQL = new SqlCommand(strServiceContent, myConnection);
                cmdBuildingSQL.Parameters.Add(new SqlParameter("@table_name", strServiceTargetTable));
                cmdBuildingSQL.Parameters.Add(new SqlParameter("@type", strServiceType));
                cmdBuildingSQL.Parameters.Add(new SqlParameter("@conditions", strServiceConditions));
                cmdBuildingSQL.Parameters.Add(new SqlParameter("@Sortby", strSortby));
                cmdBuildingSQL.Parameters.Add(new SqlParameter("@resultsql_1", SqlDbType.NVarChar, -1));
                cmdBuildingSQL.Parameters.Add(new SqlParameter("@resultsql_2", SqlDbType.NVarChar, -1));
                cmdBuildingSQL.Parameters["@resultsql_1"].Direction = ParameterDirection.Output;
                cmdBuildingSQL.Parameters["@resultsql_2"].Direction = ParameterDirection.Output;

                tableadapter = new SqlDataAdapter(cmdBuildingSQL);
                tableadapter.Fill(setGlobal, "CommandBuildingTable");
                strSQL = (String)cmdBuildingSQL.Parameters["@resultsql_1"].Value;
                strtable_name_list = (string)cmdBuildingSQL.Parameters["@resultsql_2"].Value;
                //Tracer.Verbose("BuildAndRunCmdSQL.strSQL", strSQL);
                cmdSQL = new SqlCommand(strSQL, myConnection);
                cmdSQL = BuildSQLParameters(cmdSQL, strServiceType, dwForeignKeyList, dwRequest);
                tableadapter = new SqlDataAdapter(cmdSQL);
                dsResult = new DataSet();
                tableadapter.Fill(dsResult, strServiceTargetTable);
                dsResult = NameTable(strServiceTargetTableID, strServiceConditions, dsResult, strtable_name_list);
                //Tracer.Verbose("BuildAndRunCmdSQL.dsResult", dsResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsResult;
        }
        private SqlCommand BuildSQLParameters(SqlCommand cmdSQL, string strServiceType, DataRow dwForeignKeyList, DataRow dwRequest)
        {
            if (dwRequest == null)
                return cmdSQL;
            string TBKey;
            string ForeignKey = dwForeignKeyList["FOREIGNKEY"].ToString().Trim();
            string TBName = dwRequest.Table.TableName;
            System.DateTime TimeNow = System.DateTime.Now;
            string sValue = "";
            try
            {
                #region CaseWise GDKEYID/TBNameKey

                if (strServiceType.Contains("UPDATE")
                || strServiceType.Contains("INSERT"))
                {
                    if (dwRequest.Table.Columns.Contains("GDKEYID"))
                    {
                        sValue = dwRequest["GDKEYID"].ToString().Trim();
                        if (sValue == "")
                            dwRequest["GDKEYID"] = dwRequest["POS"];
                    }
                    if (dwRequest.Table.Columns.Contains((TBName + "ID").ToUpper()))
                    {
                        sValue = dwRequest[TBName.ToUpper() + "ID"].ToString().Trim();
                        if (sValue == "")
                            dwRequest[TBName.ToUpper() + "ID"] = dwRequest["POS"];
                    }
                    if (dwRequest.Table.Columns.Contains(TBName + "Key")
                        && TBName != "GTPREDEF")
                    {
                        sValue = dwRequest[TBName.ToUpper() + "KEY"].ToString().Trim();
                        if (sValue == "")
                            dwRequest[TBName.ToUpper() + "KEY"] = dwRequest["GDKEY"];
                    }
                }
                #endregion

                if (strServiceType.Contains("UPDATE")
                    || strServiceType.Contains("INSERT")
                    || strServiceType.Contains("SELECT_LIKE")
                    || ForeignKey.Contains("_LikeAny"))
                {
                    foreach (DataColumn dcTemp in dwRequest.Table.Columns)
                    {
                        TBKey = dcTemp.ColumnName;
                        cmdSQL.Parameters.Add(new SqlParameter("@" + TBKey, dwRequest[TBKey].ToString()));
                    }
                    TimeNow = System.DateTime.Now;
                    if (strServiceType.Contains("INSERT"))
                    {
                        cmdSQL.Parameters["@FDATE"].Value = TimeNow;
                    }
                    cmdSQL.Parameters["@LDATE"].Value = TimeNow;
                }
                else
                {
                    for (int KeyI = 0; KeyI < 10; KeyI++)
                    {
                        string KeyName = "KEY" + KeyI.ToString();
                        TBKey = dwForeignKeyList[KeyName].ToString().Trim();
                        if (TBKey != "")
                        {
                            cmdSQL.Parameters.Add(new SqlParameter("@" + TBKey, dwRequest[TBKey].ToString()));
                        }
                    }
                    TimeNow = System.DateTime.Now;
                    if (!cmdSQL.Parameters.Contains("@FUSER"))
                    {
                        cmdSQL.Parameters.Add(new SqlParameter("@FUSER", "LANSUN"));
                        cmdSQL.Parameters.Add(new SqlParameter("@FDATE", TimeNow));
                        cmdSQL.Parameters.Add(new SqlParameter("@LUSER", "LANSUN"));
                        cmdSQL.Parameters.Add(new SqlParameter("@LDATE", TimeNow));
                    }
                    else
                    {
                        cmdSQL.Parameters["@FDATE"].Value = TimeNow;
                        cmdSQL.Parameters["@LDATE"].Value = TimeNow;
                    }
                }

                if (!strServiceType.Contains("SELECT") && !strServiceType.Contains("STRUCTURE"))
                {
                    cmdSQL = Build(cmdSQL, dwRequest);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            //Tracer.Verbose("BuildAndRunCmdSQL.dsResult", cmdSQL.CommandText);
            return cmdSQL;
        }
        private DataSet NameTable(string strServiceTargetTableID, string strServiceConditions, DataSet dsSource, string strtable_name_list)
        {
            string strServiceTargetTable;

            try
            {

                int strPosX = 0;
                int strPosY = 0;
                int i = 0;
                int iTmp = 0;

                if (dsSource.Tables.Count > 0)
                {
                    while (strtable_name_list != "")
                    {
                        iTmp = strtable_name_list.Length;
                        strPosX = strtable_name_list.IndexOf(";");
                        strServiceTargetTable = strtable_name_list.Substring(0, strPosX + 1);
                        strtable_name_list = strtable_name_list.Substring(strPosX + 1, iTmp - (strPosX + 1));
                        //strtable_name_list = strtable_name_list.Replace(strServiceTargetTable, "");
                        strServiceTargetTable = strServiceTargetTable.Replace(";", "");

                        strPosY = strServiceConditions.IndexOf(";");
                        if (strPosY == -1)
                        {
                            strServiceConditions = strServiceTargetTableID + ";";
                            strPosY = strServiceConditions.IndexOf(";");
                        }
                        strServiceTargetTableID = strServiceConditions.Substring(0, strPosY + 1);
                        strServiceConditions = strServiceConditions.Replace(strServiceTargetTableID, "");
                        strServiceTargetTableID = strServiceTargetTableID.Replace(";", "");

                        if (dsSource.Tables[i] != null)
                        {
                            dsSource.Tables[i].TableName = strServiceTargetTable;
                            dsSource.Tables[i].DisplayExpression = strServiceTargetTableID;
                            dsSource.Tables[i].ExtendedProperties.Add("MakeUp", "NO");
                        }
                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsSource;
        }
 
        private Queue GetPrimaryKey(string TBName)
        {
            Queue cmbPath = new Queue();
            try
            {
                DataTable ForeignKeyList = this.setGlobal.Tables["ForeignKeyList"];

                DataRow[] dwPK = ForeignKeyList.Select("TYPE='PrimaryKey' and TBNAME='" + TBName + "' and FOREIGNKEY='PK_" + TBName + "'");

                for (int i = 0; i < 10; i++)
                {
                    string value = dwPK[0]["KEY" + i.ToString()].ToString().Trim();
                    if (value != "")
                    {
                        cmbPath.Enqueue(value);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

            return cmbPath;

        }
    }
}